<?php
/**
 * User: songlin
 * Date: 2017/10/19
 * PHPExcel 数据导出
 */

actionPhpexcel();

function actionPhpexcel()
{
    require_once 'PHPExcel/PHPExcel.php';

    define('EOL', '<br />');
    $objPHPExcel = new PHPExcel();
    ini_set("memory_limit", "1024M"); // 设置php可使用内存
    set_time_limit(0);  # 设置执行时间最大值

    $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
    if (!\PHPExcel_Settings::setCacheStorageMethod($cacheMethod)) {
        die($cacheMethod . " 缓存方法不可用" . EOL);
    }
    echo date('H:i:s'), " 当前使用的缓存方法是： ", $cacheMethod, " 方式", EOL;
    echo date('H:i:s'), " 开始设置文档属性", EOL;
    $objPHPExcel->getProperties()
        ->setCreator("dcb3688")//创建者
        ->setLastModifiedBy("dcb3688")//最后修改者
        ->setTitle("客户信息记录")
        ->setSubject("客户信息Document")
        ->setDescription("描述……")
        ->setKeywords("office 2007  php")
        ->setCategory("产品信息AAA");

    echo date('H:i:s'), " 开始添加单元格标题", EOL;
    $objPHPExcel->setActiveSheetIndex(0);
    $objPHPExcel->getActiveSheet()->setCellValue('A1', "客户姓名");
    $objPHPExcel->getActiveSheet()->setCellValue('B1', "性别");
    $objPHPExcel->getActiveSheet()->setCellValue('C1', "年龄");
    $objPHPExcel->getActiveSheet()->setCellValue('D1', "联系电话");
    $objPHPExcel->getActiveSheet()->setCellValue('E1', "地址");
    $objPHPExcel->getActiveSheet()->setCellValue('F1', "血型");
    $objPHPExcel->getActiveSheet()->setCellValue('G1', "创建时间");

    /**
     * 单元格宽度
     */
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(6);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(6);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(18);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(6);
    $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);
//    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setAutoSize(true);

    /**
     * 左对齐与 右对齐
     * 可设置整列->getStyle('N')   可针对行rows设置getStyle('N3')
     */
//    $objPHPExcel->getActiveSheet()->getStyle('N')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
//    $objPHPExcel->getActiveSheet()->getStyle('N3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

    #####################################开始添加数据###############################################################

    /**
     * 分页分时间： 微软Execl最大值6万行， total/60000=文件个数，  limit 60000, 60000
     */
    $data = array(
        'a' => '林松',
        'b' => '男',
        'c' => '23',
        'd' => '18150167782',
        'e' => '厦门市思明区厦禾路844号中厦国际大厦17楼点击网络公司',
        'f' => 'O',
//        'g'=>'time',
    );
    if (!empty($data)) {
        for ($i = 2; $i < 200000; $i++) {
            $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, $data['a']);
            $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $data['b']);
            $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $data['c']);
            $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $data['d']);
            $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $data['e']);
            $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $data['f']);
            $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, date('Y-m-d H:i:s'));
        }
    } else {
        die(" 暂无数据" . EOL);
    }

    #######################################################################################################

    $ExcelName = 'test' . date('-Y-m-d-H-i-s');//文件命名

    $objPHPExcel->getActiveSheet()->setTitle('客户信息');
    echo date('H:i:s'), " 设置格式为Excel2007版格式", EOL;
    $callStartTime = microtime(true);

    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('data/' . $ExcelName . '.xlsx');//这里设置保存路径
    $callEndTime = microtime(true);
    $callTime = $callEndTime - $callStartTime;

    echo date('H:i:s'), " 设置生成的文件为： ", pathinfo($ExcelName, PATHINFO_BASENAME) . '.xlsx', EOL;
    echo date('H:i:s'), ' 写入Workbook中耗时 ', sprintf('%.4f', $callTime), " 秒", EOL;
    echo date('H:i:s'), ' 当前内存使用情况: ', (memory_get_usage(true) / 1024 / 1024), " MB", EOL;
    echo date('H:i:s'), " 内存使用峰值: ", (memory_get_peak_usage(true) / 1024 / 1024), " MB", EOL;
    echo date('H:i:s'), " 完成写入文件", EOL;
    echo date('H:i:s'), ' 文件被创建在： ', getcwd() . '\data\\', '目录', EOL;
}